package com.common.www.service.programme;


import org.springframework.boot.base.jpa.service.BaseLogicService;
import com.common.www.bean.entity.programme.ContrastScheme;
import com.common.www.bean.entity.programme.ProjectScheme;
import com.common.www.bean.entity.programme.SupplyCompanyScheme;
import com.common.www.bean.entity.project.Project;
import com.common.www.repository.programme.ContrastSchemeRepository;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;
import java.util.stream.Collectors;

import static com.common.www.service.tabulation.InnerTabulationService.createCellAddStype;

@Service
@Transactional
public class ContrastSchemeService extends BaseLogicService<ContrastScheme, Long> {
    @Autowired
    private ContrastSchemeRepository repository;

    @Override
    public ContrastSchemeRepository getRepository() {
        return repository;
    }

    /**
     * 导入调研项目方案
     *
     * @param workbook
     * @return
     * @throws Exception
     */
    public List<ContrastScheme> importSimulate(Workbook workbook, Project project, String fileName) throws Exception {
        Sheet sheet = null;
        if (workbook.getNumberOfSheets() > 0) {
            sheet = workbook.getSheetAt(0);
        }
        if (sheet == null) {
            throw new Exception("未找到导入数据,请检查文档");
        }
        List<ContrastScheme> list = new ArrayList<>();

        for (int rowIndex = 3; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
//            System.out.println("第 " + rowIndex + " 行");
            String r0 = null;
            String r1 = null;
            String r2 = null;
            String r3 = null;
            String r4 = null;

            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                break;
            }
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                cell.setCellType(CellType.STRING);
                String value = cell.getStringCellValue();

                if (cell.getColumnIndex() == 0)
                    r0 = value.trim();
                if (cell.getColumnIndex() == 1)
                    r1 = value.trim();
                if (cell.getColumnIndex() == 2)
                    r2 = value.trim();
                if (cell.getColumnIndex() == 3)
                    r3 = value.trim();
                if (cell.getColumnIndex() == 4)
                    r4 = value.trim();
            }

            if ("合计".equals(r0)) {
                continue;
            } else {
                ContrastScheme io = new ContrastScheme();
                io.setSort(Integer.valueOf(r0));
                io.setCategory(r1);
                io.setMinorItems(r2);
                io.setDetailsTerm(r3);
                io.setInviteTendersScheme(r4);
                io.setProject(project);
                io.setInviteTendersSchemeFileName(fileName);
                list.add(io);
            }
        }
        return list;
    }


    /**
     * 按条件清空招标方案
     *
     * @param projectId
     */
    public void conditionEmptyInviteTenders(Long projectId) {
        HashMap hashMap = new HashMap();
        hashMap.put("project.id", projectId);
        List<ContrastScheme> companySchemes = queryToList(hashMap, null);
        for (ContrastScheme scheme : companySchemes) {
            scheme.setInviteTendersScheme(null);
            scheme.setInviteTendersSchemeFileName(null);
            saveAndFlush(scheme);
        }
    }


    /**
     * 组装电力设计方案
     *
     * @param contrastSchemeList
     * @param projectSchemeList
     * @param supplyCompanySchemeList
     * @return
     */
    public Map<String, Object> getContrastSchemeAll(List<ContrastScheme> contrastSchemeList, List<ProjectScheme> projectSchemeList, List<SupplyCompanyScheme> supplyCompanySchemeList) {
        //返回数据准备
        List<Map<String, Object>> pNames = new ArrayList<>();
        List<Map<String, Object>> sNames = new ArrayList<>();
        //外部调研项目方案组装
        Map<String, List<ProjectScheme>> assignProjectNameList = projectSchemeList.stream().collect(Collectors.groupingBy(ProjectScheme::getAssignProjectName));
        for (Map.Entry<String, List<ProjectScheme>> item : assignProjectNameList.entrySet()) {
            List<ProjectScheme> value = item.getValue();
            Map<String, Object> itemMap = new HashMap<>();
            itemMap.put("pValue", value);
            itemMap.put("pName", item.getKey());
            pNames.add(itemMap);
        }
        //供应商方案-分阶段组装
        Map<String, List<SupplyCompanyScheme>> supplyCompanyNameList = supplyCompanySchemeList.stream().collect(Collectors.groupingBy(SupplyCompanyScheme::getSupplyCompanyName));
        for (Map.Entry<String, List<SupplyCompanyScheme>> item : supplyCompanyNameList.entrySet()) {
            List<SupplyCompanyScheme> value = item.getValue();
            Map<String, Object> itemMap = new HashMap<>();
            itemMap.put("sValue", value);
            itemMap.put("sName", item.getKey());
            sNames.add(itemMap);
        }
        Map<String, Object> tempMap = new HashMap<>();
        tempMap.put("pNames", pNames);
        tempMap.put("sNames", sNames);
        tempMap.put("contrastSchemeList", contrastSchemeList);
        tempMap.put("projectSchemeList", projectSchemeList);
        tempMap.put("supplyCompanySchemeList", supplyCompanySchemeList);
        return tempMap;
    }


    /**
     * 导出设计方案对比
     *
     * @param tempMap
     * @return
     */
    public XSSFWorkbook exportScheme(Map<String, Object> tempMap) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("电力设计方案");
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 居中
        // 创建日期格式
        CellStyle cellDateStyle = workbook.createCellStyle();
        CreationHelper createHelper = workbook.getCreationHelper();
        cellDateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
        Row row0 = sheet.createRow(0);
        createCellAddStype(row0, 0, cellStyle).setCellValue("电力设计方案对比及调研表");

        CellRangeAddress titleRegion = new CellRangeAddress(0, 0, 0, 12);// 下标从0开始 起始行号，终止行号， 起始列号，终
        sheet.addMergedRegionUnsafe(titleRegion);
        //边框
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellDateStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellDateStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellDateStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellDateStyle.setBorderRight(BorderStyle.THIN);//右边框

        // 生成表头
        if (tempMap.get("contrastSchemeList") != null) {
            Row row1 = sheet.createRow(1);
            createCellAddStype(row1, 0, cellStyle).setCellValue("序号");
            createCellAddStype(row1, 1, cellStyle).setCellValue("类别");
            createCellAddStype(row1, 2, cellStyle).setCellValue("项目");
            createCellAddStype(row1, 3, cellStyle).setCellValue("细项");
            createCellAddStype(row1, 4, cellStyle).setCellValue("招标方案");
            int indexA = 5;
            if (tempMap.get("sNames") instanceof Collection) {
                Collection<Object> sNames = (Collection<Object>) tempMap.get("sNames");
                if (sNames.size() == 0){
                    createCellAddStype(row1, indexA++, cellStyle).setCellValue("投标方案");
                }
                for (Object sNameObj : sNames) {
                    if (sNameObj instanceof Map) {
                        Map<String, Object> sNameMap = (Map<String, Object>) sNameObj;
                        createCellAddStype(row1, indexA++, cellStyle).setCellValue("投标方案");
                    }
                }
            }
            createCellAddStype(row1, indexA++, cellStyle).setCellValue("理论最优方案");
            createCellAddStype(row1, indexA++, cellStyle).setCellValue("预估定标方案（定向沟通后）与招标过程中理论最优方案对比\n" + "（结合未中标单位投标方案及外部调研情况）");
            createCellAddStype(row1, indexA++, cellStyle).setCellValue("预估定标方案（定向沟通后）与招标过程中理论最优方案对比\n" + "（结合未中标单位投标方案及外部调研情况）");
            createCellAddStype(row1, indexA++, cellStyle).setCellValue("预估定标方案（定向沟通后）与招标过程中理论最优方案对比\n" + "（结合未中标单位投标方案及外部调研情况）");
            if (tempMap.get("pNames") instanceof Collection) {
                Collection<Object> pNames = (Collection<Object>) tempMap.get("pNames");
                if (pNames.size() == 0){
                    createCellAddStype(row1, indexA++, cellStyle).setCellValue("外部项目调研");
                }
                for (Object pNameObj : pNames) {
                    if (pNameObj instanceof Map) {
                        Map<String, Object> pNameMap = (Map<String, Object>) pNameObj;
                        createCellAddStype(row1, indexA++, cellStyle).setCellValue("外部项目调研");
                    }
                }
            }
        }

        // 生成第二行
        Row row2 = sheet.createRow(2);
        createCellAddStype(row2, 0, cellStyle).setCellValue("序号");
        createCellAddStype(row2, 1, cellStyle).setCellValue("类别");
        createCellAddStype(row2, 2, cellStyle).setCellValue("项目");
        createCellAddStype(row2, 3, cellStyle).setCellValue("细项");
        createCellAddStype(row2, 4, cellStyle).setCellValue("招标方案");
        int indexB = 5;
        // 供应商（投标方案）
        int sCount = 1;
        if (tempMap.get("sNames") instanceof Collection) {
            Collection<?> sNamesCollection = (Collection<?>) tempMap.get("sNames");
            if (sNamesCollection.size() == 0){
                createCellAddStype(row2, indexB++, cellStyle).setCellValue("");
            }else {
                sCount = sNamesCollection.size();
            }
            for (Object sNameObj : sNamesCollection) {
                if (sNameObj instanceof Map) {
                    Map<String, Object> sNameMap = (Map<String, Object>) sNameObj;
                    createCellAddStype(row2, indexB++, cellStyle).setCellValue((String) sNameMap.get("sName"));
                }
            }
        }
        createCellAddStype(row2, indexB++, cellStyle).setCellValue("理论最优方案");
        createCellAddStype(row2, indexB++, cellStyle).setCellValue("评估后拟定标方案");
        createCellAddStype(row2, indexB++, cellStyle).setCellValue("评估后拟定标方案-理论最优方案的差异金额（以市场价测算/万元）");
        createCellAddStype(row2, indexB++, cellStyle).setCellValue("差异分析");
        // 调研项目(外部项目调研)
        int pCount = 1;
        if (tempMap.get("pNames") instanceof Collection) {
            Collection<?> pNamesCollection = (Collection<?>) tempMap.get("pNames");
            if (pNamesCollection.size() == 0){
                createCellAddStype(row2, indexB++, cellStyle).setCellValue("");
            }else {
                pCount = pNamesCollection.size();
            }
            for (Object pNameObj : pNamesCollection) {
                if (pNameObj instanceof Map) {
                    Map<String, Object> pNameMap = (Map<String, Object>) pNameObj;
                    createCellAddStype(row2, indexB++, cellStyle).setCellValue((String) pNameMap.get("pName"));
                }
            }
        }

        CellRangeAddress cra1 = new CellRangeAddress(1, 2, 0, 0);
        CellRangeAddress cra2 = new CellRangeAddress(1, 2, 1, 1);
        CellRangeAddress cra3 = new CellRangeAddress(1, 2, 2, 2);
        CellRangeAddress cra4 = new CellRangeAddress(1, 2, 3, 3);
        CellRangeAddress cra5 = new CellRangeAddress(1, 2, 4, 4);
        CellRangeAddress cra6 = new CellRangeAddress(1, 2, 5+sCount, 5+sCount);
        CellRangeAddress cra7 = new CellRangeAddress(1, 1, 5, 4+sCount);
        CellRangeAddress cra8 = new CellRangeAddress(1, 1, 6+sCount, 8+sCount);
        CellRangeAddress cra9 = new CellRangeAddress(1, 1, 9+sCount, 8+sCount+pCount);
        //在sheet里增加合并单元格
        sheet.addMergedRegion(cra1);
        sheet.addMergedRegion(cra2);
        sheet.addMergedRegion(cra3);
        sheet.addMergedRegion(cra4);
        sheet.addMergedRegion(cra5);
        sheet.addMergedRegion(cra6);
        if (4+sCount != 5){
            sheet.addMergedRegion(cra7);
        }
        sheet.addMergedRegion(cra8);
        if (9+sCount !=8+sCount+pCount){
            sheet.addMergedRegion(cra9);
        }

        // 生成数据行
        Integer rowIndex = 3;
        if (tempMap.get("contrastSchemeList") instanceof Collection) {
            Collection<?> contrastSchemeList = (Collection<?>) tempMap.get("contrastSchemeList");
            for (Object obj : contrastSchemeList) {
                if (obj instanceof ContrastScheme) {
                    ContrastScheme item = (ContrastScheme) obj;
                    Row row = sheet.createRow(rowIndex);
                    createCellAddStype(row, 0, cellStyle).setCellValue(item.getSort());
                    createCellAddStype(row, 1, cellStyle).setCellValue(item.getCategory());
                    createCellAddStype(row, 2, cellStyle).setCellValue(item.getMinorItems());
                    createCellAddStype(row, 3, cellStyle).setCellValue(item.getDetailsTerm());
                    createCellAddStype(row, 4, cellStyle).setCellValue(item.getInviteTendersScheme());
                    int indexC = 5;
                    // 供应商（投标方案）
                    if (tempMap.get("sNames") instanceof Collection) {
                        Collection<?> sNamesCollection = (Collection<?>) tempMap.get("sNames");
                        for (Object sNameObj : sNamesCollection) {
                            if (sNameObj instanceof Map) {
                                Map<String, Object> sNameMap = (Map<String, Object>) sNameObj;
                                Collection<?> sValueCollection = (Collection<?>) sNameMap.get("sValue");
                                for (Object sValueObj : sValueCollection) {
                                    // sValueObj 是自定义对象
                                    if (sValueObj instanceof SupplyCompanyScheme) {
                                        SupplyCompanyScheme sValue = (SupplyCompanyScheme) sValueObj;
                                        if (sValue.getSort().equals(item.getSort())) {
                                            createCellAddStype(row, indexC++, cellStyle).setCellValue(sValue.getDetailsContent());
                                        }
                                    }
                                }
                            }
                        }
                    }
                    createCellAddStype(row, indexC++, cellStyle).setCellValue(item.getOptimalSolution());
                    createCellAddStype(row, indexC++, cellStyle).setCellValue(item.getProposedPlan());
                    createCellAddStype(row, indexC++, cellStyle).setCellValue(item.getDifferenceAmount());
                    createCellAddStype(row, indexC++, cellStyle).setCellValue(item.getDifferentialAnalysis());
                    // 调研项目(外部项目调研)
                    if (tempMap.get("pNames") instanceof Collection) {
                        Collection<?> pNamesCollection = (Collection<?>) tempMap.get("pNames");
                        for (Object pNameObj : pNamesCollection) {
                            if (pNameObj instanceof Map) {
                                Map<String, Object> pNameMap = (Map<String, Object>) pNameObj;
                                Collection<?> pValueCollection = (Collection<?>) pNameMap.get("pValue");
                                for (Object pValueObj : pValueCollection) {
                                    // pValueObj 是自定义对象
                                    if (pValueObj instanceof ProjectScheme) {
                                        ProjectScheme pValue = (ProjectScheme) pValueObj;
                                        if (pValue.getSort().equals(item.getSort())) {
                                            createCellAddStype(row, indexC++, cellStyle).setCellValue(pValue.getDetailsContent());
                                        }
                                    }
                                }
                            }
                        }
                    }
                    rowIndex++;
                }
            }
        }
        return workbook;
    }


}
